package cn.zbmao.web.dao;

import cn.zbmao.fc.FcTool;
import cn.zbmao.web.bean.Constants;
import cn.zbmao.web.entity.TenderNews;
import cn.zbmao.web.util.DateUtil;
import cn.zbmao.web.util.StringOperateUtil;
import cn.zbmao.web.util.dao.DaoUtil;
import cn.zbmao.web.util.dao.SqlUtil;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import org.apache.commons.lang.StringUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class NewsDao implements TenderNewsDao{
    /**
     * 用户关心消息 取最新10条
     *
     * @param topNum
     *            取数据的数量
     * @param map
     *            用户关心数据
     * @return
     */

    public List<Record> findNewsTop(int topNum, Map<String, List<Record>> map,
                                    String sortKey) {
        StringBuffer sb = new StringBuffer();
        //t1.pid                                                                                                     t1.num
        sb.append("select t1.id,t1.tender_news_id as pid,t1.title,t1.url,t1.subscriber,t1.host_id,t1.stime,t1.etime,t1.t1.tender_id as num,FROM_UNIXTIME(t1.etime,'%Y-%m-%d') as endTime,FROM_UNIXTIME(t1.stime,'%Y-%m-%d') as startTime,t1.timelong "
                + "from "+ Constants.TENDER_NEWS_INFO+" as t1  where ");
        //tendernews_info                           //gd
        String s = SqlUtil.getNewsWithUser(map, "t1.");
        // 添加用户设置条件
        if (!s.isEmpty() && !s.endsWith("and")) {
            sb.append(s + " and");
        } else {
            sb.append(s);
        }
        // t1.province
        sb.append(" order by t1.");
        sb.append(sortKey);
        sb.append(" desc limit ?");
        return Db.find(sb.toString(), topNum);
    }

    /**
     * 用户自定义分页
     *
     * @param pags
     *            页数
     * @param topNum
     *            每页数量
     * @param map
     *            用户关心数据
     * @param staleDated
     *            是否过期
     * @return
     */
    public Page<Record> findNewsPage(Integer pags, int topNum,
                                     Map<String, List<Record>> map, long staleDated) {
        StringBuffer sb = new StringBuffer();
        sb.append("from "+ Constants.TENDER_NEWS_INFO+" as t1  where ");
        // tendernews_info                          //gd                    //t1.province
        String s = SqlUtil.getNewsWithUser(map, "t1.");

//        String str=SqlUtil.getNewsWithUserAnd(map, "t1.");
        // 添加用户设置条件
        if (!s.isEmpty() && !s.endsWith("and")) {
            sb.append(s + " and");
        } else {
            sb.append(s);
        }

        int newtime = DateUtil.getTodayToInt();
        if (staleDated == DaoUtil.unStaleDated) {
            sb.append(" t1.etime>=" + newtime + " ");
        } else if (staleDated == DaoUtil.staleDated_experience) {
            sb.append(" t1.etime<" + newtime + " ");
            int lastmo = newtime-3600*24*90;
            sb.append(" and t1.etime>"+lastmo+" ");
        }else if (staleDated == DaoUtil.staleDated) {
            sb.append(" t1.etime<" + newtime + " ");
        }

        sb.append(" and t1.status=0 order by t1.etime desc");

        return Db
                .paginate(
                        pags,
                        topNum,                 //t1.pid                                                            //t1.num
                        "select t1.id,t1.tender_news_id as pid,t1.title,t1.host_id,t1.url,t1.tender_id as num,t1.subscriber,t1.item_amount,t1.stime,t1.etime,t1.qualification,FROM_UNIXTIME(t1.etime,'%Y-%m-%d') as endTime,FROM_UNIXTIME(t1.stime,'%Y-%m-%d') as startTime,t1.timelong ",
                        sb.toString());

    }

    /**
     *
     * @param id 招标Id
     * @return 招标信息
     */
    public Record findNews(int id) {
//        通过招标Id查询出一条招标信息
        Record record = Db
                .findFirst(
                        "select *,FROM_UNIXTIME(etime,'%Y-%m-%d') as endTime,FROM_UNIXTIME(stime,'%Y-%m-%d') as startTime from  " + Constants.TENDER_NEWS_INFO + " where id = ?",
                        //  tendernews_info
                        id);
        //查出招标信息中的招标编号，并在中标信息表中通过招标编号查询中标信息，有个话，将中标Id插出Rcord中，没有就插入0
        if (record.getStr("tender_id") != null && !record.getStr("tender_id").isEmpty()) {
            Record result_record = Db.findFirst(
                    "select id FROM " + Constants.SUCCESSFUL_TENDER_NEWS_INFO + " where tender_id=? ",
                    //tenderresultnews_info
                    record.getStr("tender_id"));
            if (result_record != null) {
                record.set("tenderresultnews_info_id",
                        result_record.getInt("id"));
            } else {
                record.set("tenderresultnews_info_id", 0);
            }
        } else {
            record.set("tenderresultnews_info_id", 0);
        }

        return record;
    }

    /**
     *
     * @param id 中标Id
     * @return 中标信息
     */
    public Record find_result_news(int id) {
        Record result_record = Db
                .findFirst(                        //tender_result_end_time
                        "select *,FROM_UNIXTIME(successful_time,'%Y-%m-%d') as time from  " + Constants.SUCCESSFUL_TENDER_NEWS_INFO + " where id = ?",
                        //tenderresultnews_info
                        id);
        //查出中标信息中的中标编号，并在招标信息表中通过中标编号查询招标信息，有个话，将招标Id插出Rcord中，没有就插入0
        if (result_record.getStr("tender_id") != null
                && !result_record.getStr("tender_id").isEmpty()) {
            Record tender_Record = Db.findFirst(                            //num
                    "select id FROM " + Constants.TENDER_NEWS_INFO + " where tender_id=? ",
                    //tendernews_info
                    result_record.getStr("tender_id"));
            if (tender_Record != null) {
                result_record.set("tendernews_info_id",
                        tender_Record.getInt("id"));
            } else {
                result_record.set("tendernews_info_id", 0);
            }
        } else {
            result_record.set("tendernews_info_id", 0);
        }

        return result_record;
    }

    /**
     *
     * @param id 招标Id
     * @return
     * 通过招标Id查询出招标的Html，content等数据
     */
    public Record querySnapShot(int id) {
        return Db.findById(Constants.TENDER_NEWS_HTML, "tender_news_id", id);
    }

    /**
     *
     * @param id 招标Html表Id
     * @return
     * 通过招标HtmlId查询出招标的Url
     */
    public Record queryUrl(int id){
        return  Db.findFirst("select url from "+Constants.TENDER_NEWS_HTML+" tnh JOIN "+Constants.TENDER_NEWS+" tn on tnh.tender_news_id = tn.id where tnh.id=?",id);
    }
//    public Record queryUrl_z(int id){
//        return  Db.findFirst("select url from "+Constants.SUCCESSFUL_TENDER_NEWS_HTML+" tnh JOIN "+Constants.SUCCESSFUL_TENDER_NEWS+" tn on tnh.successful_tender_news_id = tn.id  where tn.id=?",id);
//    }

    /**
     *
     * @param id 中标Id
     * @return
     * 通过中标Id查询中标Info表中的Url
     */
    public Record queryUrl_z(int id){
        return  Db.findFirst("select url from "+Constants.SUCCESSFUL_TENDER_NEWS_INFO+"  where  successful_tender_news_id=?",id);
    }

    /**
     *
     * @param level 类别查询中的层级
     * @param parentId 父辈Id
     * @return
     * 查询该层级下,父辈Id都为parentId的数据
     */
    public List<Record> find_industry_parentId(int level,int parentId){
        String sql="select id,industry_name as name from tender_news_industry where level=? and parent_id=?";

        return Db.find(sql, new Object[]{level, parentId});
    }

    /**
     *
     * @param id 中标Id
     * @return
     * 通过中标Id查询中标的HTML信息
     */
    public Record result_snapShot(int id) {
        return Db.findById(Constants.SUCCESSFUL_TENDER_NEWS_HTML, "successful_tender_news_id", id);
    }

    /**
     *
     * @param level 层级
     * @return
     * 查询出该层级下的所以Id与名称
     */
    public List<Record> find_industry(int level){
        String sql="select id,industry_name as name from tender_news_industry where level=?";

        return  Db.find(sql, new Object[]{level});
    }


    private final static String SEARCH_TYPE_QUANWEN = "content_fc";
    private final static String SEARCH_TYPE_BIAOTI = "title_fc";
    // 搜索查询

    /**
     *
     * @param pags 当前页数
     * @param topNum 每页最多显示行书
     * @param title_key_order 按标题排序 当为1时正序，为2时倒序
     * @param sTime_order  按开始日期排序
     * @param eTime_order  按截止日期排序
     * @param qualification_order 按资质排序
     * @param subscriber_order 按招标用户单位排序
     * @param agent_order
     * @param itemAmount_order 按金额排序
     * @param search_key 关键字
     * @param search_subscriber 公司
     * @param startTime 开始时间
     * @param endTime 截止时间
     * @param itemAmount 金额
     * @param tender_type 招标中标类型
     * @param territory 省份
     * @param isOverdue 是否过期
     * @param orderby
     * @param search_type 判断按全文搜索还是按照标题搜索
     * @return
     */
    public Page<Record> search_list(Integer pags, int topNum,int title_key_order,int sTime_order,int eTime_order,int qualification_order,int subscriber_order, int agent_order,int itemAmount_order,
                                    String search_key, String search_subscriber, int startTime,
                                    int endTime,int itemAmount, int tender_type,int territory,int isOverdue,int orderby,int search_type,String [] qualification,int loca) {

        //全文搜索
        String searchTypeStr;
        if (search_type==1){
            searchTypeStr=SEARCH_TYPE_QUANWEN;
        }else {
            searchTypeStr=SEARCH_TYPE_BIAOTI;
        }

        StringBuffer sb = new StringBuffer();                                   //t1.pid                                                //t1.num
        StringBuffer select = new StringBuffer("select t1.qualification,t1.id,t1.tender_news_id as pid,t1.title,t1.url,t1.subscriber,t1.tender_id as num,t1.host_id,FROM_UNIXTIME(t1.etime,'%Y-%m-%d') as endTime,FROM_UNIXTIME(t1.stime,'%Y-%m-%d') as startTime,t1.timelong,t1.stime,t1.etime,t1.item_amount");
        sb.append(" from "+ Constants.TENDER_NEWS_INFO+" as t1 where ");
        //tendernews_info
//        String s = SqlUtil.getNewsWithUser(map, "t1.");
//        // 添加用户设置条件
//        if (!s.isEmpty() && !s.endsWith("and")) {
//            sb.append(s + " and");
//        } else {
//            sb.append(s);
//        }

//if(search_key==null || search_key=="") {
//    List<Record> keyword = map.get("user_keyword");
//    if (keyword.size() != 0) {
//
//            sb.append(" match(t1.content_fc) against ('");
//        for (int i = 0; i < keyword.size(); i++) {
//            if (i == 0) {
//                sb.append(SqlUtil.getFCkeyword(keyword.get(i).getStr("keyword")));
//            } else {
//                sb.append(" " + SqlUtil.getFCkeyword(keyword.get(i).getStr("keyword")));
//            }
//        }
//    }
//}
//判断过期时间
         sb.append(" t1.etime>=? ");
        if (endTime != 0) {
            sb.append("and t1.etime<" + endTime + " ");
        }
//关键字搜索拼接
//        if("title_fc".equals(searchTypeStr)){
//            为标题搜索时，关键字搜索与资质搜索分开
        if (StringUtils.isNotBlank(search_key)) {
//            search_key = FcUtil.fc(search_key);
//            StringBuffer sbf=new StringBuffer();
//            String search= StringOperateUtil.reString(search_key);
//            String[] str=search.split(" ");
//            for(String sea:str) {
//                String sp = FcTool.getFc().indexFc(sea);
//                String[] s2=sp.split(" ");
//                for(int i=0;i<s2.length;i++){
//                    if(i==0 && i==s2.length-1){
//                        sbf.append(" +(+"+s2[i]+") ");
//                    }else if(i==0){
//                        sbf.append(" +(+"+s2[i]);
//                    }
//                    else if(i==s2.length-1){
//                        sbf.append(" +"+s2[i]+") ");
//                    }else{
//                        sbf.append(" +"+s2[i]);
//                    }
//                }
//            }


            sb.append("and match(t1." + searchTypeStr + " ) against('" + search_key
                    + "'in boolean mode) ");
//            select.append(",match(t1."+searchTypeStr+" ) against('"+ search_key+"'in boolean mode) ");
//        }
        }
        if(qualification!=null){
            StringBuffer sbf=new StringBuffer();
            for(int a=0;a<qualification.length;a++){
                if(a==0){

                    if(qualification.length==1){
                        sbf.append(" +("+qualification[a]+")");
                    }else{
                        sbf.append(" +("+qualification[a]);
                    }
                }else if(a==qualification.length-1){
                    sbf.append(" "+qualification[a]+")");
                }else{
                    sbf.append(" "+qualification[a]);
                }
            }
            sb.append("and match(t1."+searchTypeStr+") against('"+sbf.toString()+"' in boolean mode)");
        }

//        else{
////            为内容搜索且两者都不为空时，则在一个match中搜索，提升性能
//            StringBuffer sbf = new StringBuffer();
//            if(qualification!=null ) {
//                for (int a = 0; a < qualification.length; a++) {
//                    if (a == 0) {
//
//                        if (qualification.length == 1) {
//                            sbf.append(" +(" + qualification[a] + ")");
//                        } else {
//                            sbf.append(" +(" + qualification[a]);
//                        }
//                    } else if (a == qualification.length - 1) {
//                        sbf.append(" " + qualification[a] + ")");
//                    } else {
//                        sbf.append(" " + qualification[a]);
//                    }
//                }
//            }
////            StringBuffer sbf1=new StringBuffer();
//            if(StringUtils.isNotBlank(search_key)){
////                sbf1.append("+("+search_key+")");
////                if(StringUtils.isNotBlank(search_key)){
////                String search= StringOperateUtil.reString(search_key);
////                String[] str=search.split(" ");
////                for(String sea:str) {
////                    String sp = FcTool.getFc().indexFc(sea);
////                    String[] s2 = sp.split(" ");
////                    for (int i = 0; i < s2.length; i++) {
////                        if (i == 0 && i == s2.length - 1) {
////                            sbf1.append(" +(+" + s2[i] + ") ");
////                        } else if (i == 0) {
////                            sbf1.append(" +(+" + s2[i]);
////                        } else if (i == s2.length - 1) {
////                            sbf1.append(" +" + s2[i] + ") ");
////                        } else {
////                            sbf1.append(" +" + s2[i]);
////                        }
////                    }
////                }
////                }
//
//            }
//            if(!sbf.toString().equals("") || !search_key.equals("")) {
//                sb.append("and match(t1.content_fc) against('" + search_key + "'in boolean mode)");
//                sb.append("and match(t1.content_fc) against('" + sbf.toString() + "'in boolean mode)");
//            }

//        }

// 判断用户提交的城市是省级还是市级
    if(loca!=0) {
        if(loca%10000==0){
            sb.append("and (t1.province_id=" + loca + ")");
        }else{
            sb.append("and (t1.city_id=" + loca + ")");
        }
    }
//        sb.append(" ("+tablename+ Constants.province+"_id =" + loc.get(i).getInt("district_id"));
        //公司搜索拼接
        if (StringUtils.isNotBlank(search_subscriber)) {


//            search_subscriber = FcUtil.fcAddToList(search_subscriber);
//            search_subscriber=FcTool.getFc().indexFc(search_subscriber);
            StringBuffer sbf=new StringBuffer();
            String ss= StringOperateUtil.reString(search_subscriber);
            String[] str=ss.split(" ");
            for(String sea:str) {
                String sp = FcTool.getFc().indexFc(sea);
                String[] s2=sp.split(" ");
                for(int i=0;i<s2.length;i++){
                    if(i==0 && i==s2.length-1){
                        sbf.append(" (+"+s2[i]+") ");
                    }else if(i==0){
                        sbf.append(" (+"+s2[i]);
                    }
                    else if(i==s2.length-1){
                        sbf.append(" +"+s2[i]+") ");
                    }else{
                        sbf.append(" +"+s2[i]);
                    }
                }
            }
            sb.append("and match(t1.subscriber_fc ) against('"
                    + sbf.toString() + "' in boolean mode)");
        }

//        Sql: select t1.qualification,t1.id,t1.tender_news_id as pid,t1.title,t1.url,t1.subscriber,t1.tender_id as num,t1.host_id,FROM_UNIXTIME(t1.etime,'%Y-%m-%d') as endTime,FROM_UNIXTIME(t1.stime,'%Y-%m-%d') as startTime,t1.timelong,t1.stime,t1.etime,t1.item_amount,match(t1.content_fc ) against(' (+福保 +保派 +派出 +出所 +所保 +保税 +税区 +区办 +办公 +公点 +点配 +配套 +套设 +设施 +施工 +工程 +程信 +信息 +息化 +化设 +设备 +备采 +采购 +购公 +公告) 'in boolean mode)   from tender_news_info as t1 where  (t1.city_id =440300) and match(t1.content_fc) against ('(+计算 +算机)' in boolean mode) and t1.etime>=? and match(t1.content_fc ) against(' (+福保 +保派 +派出 +出所 +所保 +保税 +税区 +区办 +办公 +公点 +点配 +配套 +套设 +设施 +施工 +工程 +程信 +信息 +息化 +化设 +设备 +备采 +采购 +购公 +公告) 'in boolean mode)  and t1.status=0 order by t1.etime desc limit 0, 50
//        Sql: select t1.qualification,t1.id,t1.tender_news_id as pid,t1.title,t1.url,t1.subscriber,t1.tender_id as num,t1.host_id,FROM_UNIXTIME(t1.etime,'%Y-%m-%d') as endTime,FROM_UNIXTIME(t1.stime,'%Y-%m-%d') as startTime,t1.timelong,t1.stime,t1.etime,t1.item_amount  from tender_news_info as t1 where  (t1.city_id =440300) and match(t1.content_fc) against ('(+福保 +保派 +派出 +出所 +所保 +保税 +税区 +区办 +办公 +公点 +点配 +配套 +套设 +设施 +施工 +工程 +程信 +信息 +息化 +化设 +设备 +备采 +采购 +购公 +公告)' in boolean mode) and t1.etime>=?  and t1.status=0 order by t1.etime desc limit 0, 50
        // 判断是否为招标信息
        if (tender_type == 0) {
            // sb.append("and match(t1.title_fc ) against('-中标' in boolean mode) ");
        } else if (tender_type == 1) {
            sb.append("and match(t1."+searchTypeStr+" ) against('+中标' in boolean mode) ");
        }


        if (territory!=0) { // t1.province
            sb.append(" and t1.province_id = "+territory);
        }
//        搜索金额

        if(itemAmount==1){
            sb.append(" and t1.item_amount < 200000");
        }else if(itemAmount==2){
            sb.append(" and t1.item_amount >=200000 and t1.item_amount < 500000");
        }else if(itemAmount==3){
            sb.append(" and t1.item_amount >=500000 and t1.item_amount < 1000000");
        }else if(itemAmount==4){
            sb.append(" and t1.item_amount >=1000000 and t1.item_amount < 3000000");
        }else if(itemAmount==5){
            sb.append(" and t1.item_amount >=3000000 and t1.item_amount < 6000000");
        }else if(itemAmount==6){
            sb.append(" and t1.item_amount >= 6000000 and t1.item_amount <10000000");
        }else if(itemAmount==7){
            sb.append(" and t1.item_amount >= 10000000");
        }

        //搜索过期与否
        if (isOverdue!=0){
            //获取当前时间
            int nowtime= DateUtil.getTodayToInt()-1;
            if (isOverdue==1){
                sb.append(" and t1.etime>"+nowtime);
            }else{
                sb.append(" and t1.etime<="+nowtime);
            }

        }

        sb.append(" and t1.status=0 ");



//        if (orderby==1){
//            sb.append(" order by t1.etime desc");
//        }
//        按列排序
        if(title_key_order==1){
            sb.append(" order by t1.title desc ");
        }
        if(title_key_order==2){
            sb.append(" order by t1.title  ");
        }
        if(eTime_order==1){
            sb.append("order by t1.etime ");
        }
        if(eTime_order==2){
            sb.append("order by t1.etime desc");
        }
        if(sTime_order==1){
            sb.append(" order by t1.stime desc");
        }
        if(sTime_order==2){
            sb.append(" order by t1.stime ");
        }
        if(qualification_order==1){
            sb.append(" order by t1.qualification desc");
        }
        if(qualification_order==2){
            sb.append(" order by t1.qualification ");
        }
        if(subscriber_order==1){
            sb.append(" order by t1.subscriber desc");
        }
        if(subscriber_order==2){
            sb.append(" order by t1.subscriber ");
        }
        if(agent_order==1){
            sb.append(" order by t1.agent desc");
        }
        if(agent_order==2){
            sb.append(" order by t1.agent ");
        }
        if(itemAmount_order==1){
            sb.append("order by t1.item_amount desc");
        }
        if(itemAmount_order==2){
            sb.append("order by t1.item_amount ");
        }
//        默认排序方式为招标截止时间
        if(title_key_order==0 && eTime_order==0 && sTime_order==0
                &&qualification_order==0&&subscriber_order==0 &&agent_order==0 &&itemAmount_order==0){
            sb.append("order by t1.etime desc");
        }


        Page<Record> newsPage = Db
                .paginate(
                        pags,
                        topNum,
                        select.toString(),
                        sb.toString(), new Object[]{startTime});

        return newsPage;
    }

    /**
     *
     * @param pags 当前页数
     * @param topNum 每页最大数
     * @param search_subscriber 搜索公司名称
     * @param startTime 开始时间
     * @param endTime 截止时间
     * @param map 用户设置的条件
     * @param tender_type 招标中标类型
     * @param territory 省份搜索
     * @param isOverdue 判断是否过期
     * @param search_type 按标题搜索还是按正文搜索
     * @return
     */

    public Page<Record> search_list_and(Integer pags, int topNum,
                                    String search_subscriber, int startTime,
                                    int endTime, Map<String, List<Record>> map, int tender_type,int territory,int isOverdue,int search_type) {

        //全文搜索
        String searchTypeStr;
        if (search_type==1){
            searchTypeStr=SEARCH_TYPE_QUANWEN;
        }else {
            searchTypeStr=SEARCH_TYPE_BIAOTI;
        }

        StringBuffer sb = new StringBuffer();                                   //t1.pid                                                //t1.num
        StringBuffer select = new StringBuffer("select t1.qualification,t1.id,t1.tender_news_id as pid,t1.title,t1.url,t1.subscriber,t1.tender_id as num,t1.host_id,FROM_UNIXTIME(t1.etime,'%Y-%m-%d') as endTime,FROM_UNIXTIME(t1.stime,'%Y-%m-%d') as startTime,t1.timelong,t1.stime,t1.etime,t1.item_amount");
        sb.append(" from "+ Constants.TENDER_NEWS_INFO+" as t1 where ");
        //tendernews_info
        String s = SqlUtil.getNewsWithUserAnd(map, "t1.");
        // 添加用户设置条件
        if (!s.isEmpty() && !s.endsWith("and")) {
            sb.append(s + " and");
        } else {
            sb.append(s);
        }

        sb.append(" t1.etime>=? ");
        if (endTime != 0) {
            sb.append("and t1.etime<" + endTime + " ");
        }
//     公司名称搜索
        if (StringUtils.isNotBlank(search_subscriber)) {
//            search_subscriber = FcUtil.fcAddToList(search_subscriber);
            search_subscriber= FcTool.getFc().indexFc(search_subscriber);
            sb.append("and match(t1.subscriber_fc ) against('"
                    + search_subscriber + "' in boolean mode)");
        }


        // 判断是否为招标信息
        if (tender_type == 0) {
            // sb.append("and match(t1.title_fc ) against('-中标' in boolean mode) ");
        } else if (tender_type == 1) {
            sb.append("and match(t1."+searchTypeStr+" ) against('+中标' in boolean mode) ");
        }


        if (territory!=0) { // t1.province
            sb.append(" and t1.province_id = "+territory);
        }


        //搜索过期与否
        if (isOverdue!=0){
            //获取当前时间
            int nowtime= DateUtil.getTodayToInt()-1;
            if (isOverdue==1){
                sb.append(" and t1.etime>"+nowtime);
            }else{
                sb.append(" and t1.etime<="+nowtime);
            }

        }

        sb.append(" and t1.status=0 ");

        sb.append(" order by t1.etime desc");

//      System.out.println(select.toString()+sb.toString());
        Page<Record> newsPage = Db
                .paginate(
                        pags,
                        topNum,
                        select.toString(),
                        sb.toString(), new Object[]{startTime});
        return newsPage;
    }


    public List<Record> search_list_and(int startTime,int endTime, Map<String, List<Record>> map){
        StringBuffer sb = new StringBuffer();                                   //t1.pid                                                //t1.num
        StringBuffer select = new StringBuffer("select t1.qualification,t1.id,t1.tender_news_id as pid,t1.title,t1.url,t1.subscriber,t1.tender_id as num,t1.host_id,FROM_UNIXTIME(t1.etime,'%Y-%m-%d') as endTime,FROM_UNIXTIME(t1.stime,'%Y-%m-%d') as startTime,t1.timelong,t1.stime,t1.etime,t1.item_amount");
        sb.append(" from "+ Constants.TENDER_NEWS_INFO+" as t1 where ");
        //tendernews_info
        String s = SqlUtil.getNewsWithUserAnd(map, "t1.");
        // 添加用户设置条件
        if (!s.isEmpty() && !s.endsWith("and")) {
            sb.append(s + " and");
        } else {
            sb.append(s);
        }

        sb.append(" t1.etime>=? ");
        if (endTime != 0) {
            sb.append("and t1.etime<" + endTime + " ");
        }
        sb.append(" and t1.status=0 ");
        sb.append(" order by t1.etime desc limit 8");
        return Db.find(select+sb.toString(),startTime);
    }

    /**
     *
     * @param pags 当前页数
     * @param topNum 每页最大行数
     * @param title_key_order 按标题排序 当为1时正序，为2时倒序
     * @param tenderId_order 按招标编号搜索
     * @param time_order 按截止时间搜索
     * @param winBidCompany_order 按中标公司名称搜索
     * @param winBidFigure_order 按中标金额搜索
     * @param search_key 搜索关键字
     * @param search_subscriber 搜索公司名称
     * @param startTime 开始时间
     * @param endTime 截止时间
     * @param itemAmount 金额
     * @return
     * 中标内容搜索
     */
    public Page<Record> search_result_list(Integer pags, int topNum,int title_key_order,int tenderId_order,int time_order,int winBidCompany_order,int winBidFigure_order,
                                           String search_key, String search_subscriber, int startTime,
                                           int endTime,int itemAmount,int loca) {
        StringBuffer sb = new StringBuffer();                               //tender_result_id                                                                                  //t1.tender_result_end_time
        StringBuffer select = new StringBuffer("select t1.id,t1.tender_id,t1.successful_tender_news_id,t1.title,t1.url,t1.win_bid_company,t1.win_bid_figure,FROM_UNIXTIME(t1.successful_time,'%Y-%m-%d') as time,t1.successful_time");
        sb.append(" from "+ Constants.SUCCESSFUL_TENDER_NEWS_INFO+" as t1 where ");
        //tenderresultnews_info
        // String s = SqlUtil.getNewsWithUser(map, "t1.");
        // sb.append(s);//t1.type   //tender_result_end_time
        sb.append("t1.status=0 AND t1.successful_time>=? ");

        if (endTime != 0) {//tender_result_end_time
            sb.append("and t1.successful_time<" + endTime + " ");
        }
//        搜索时拼接关键字
        if (StringUtils.isNotBlank(search_key)) {
            sb.append("and match(t1.title_fc ) against('" + search_key
                    + "'in boolean mode) ");
//            search_key = FcUtil.fc(search_key);
//            search_key=FcTool.getFc().indexFc(search_key);
//            StringBuffer sbf=new StringBuffer();
//            String search= StringOperateUtil.reString(search_key);
//            String[] str=search.split(" ");
//            for(String sea:str) {
//                String sp = FcTool.getFc().indexFc(sea);
//                String[] s2=sp.split(" ");
//                for(int i=0;i<s2.length;i++){
//                    if(i==0 && i==s2.length-1){
//                        sbf.append(" (+"+s2[i]+") ");
//                    }else if(i==0){
//                        sbf.append(" (+"+s2[i]);
//                    }
//                    else if(i==s2.length-1){
//                        sbf.append(" +"+s2[i]+") ");
//                    }else{
//                        sbf.append(" +"+s2[i]);
//                    }
//                }
//            }
//
//            sb.append("and match(t1.title_fc ) against('" + sbf.toString()
//                    + "' in boolean mode) ");
//            select.append(",match(t1.title_fc ) against('" + sbf.toString()
//                    + "' in boolean mode) ");
        }
//        判断用户搜索的城市时省份还是地区
        if(loca!=0) {
            if(loca%10000==0){
                sb.append("and (t1.province_id=" + loca + ")");
            }else{
                sb.append("and (t1.city_id=" + loca + ")");
            }
        }

//        拼接公司名称
        if (StringUtils.isNotBlank(search_subscriber)) {
            StringBuffer sbf=new StringBuffer();
            String ss= StringOperateUtil.reString(search_subscriber);
            String[] str=ss.split(" ");
            for(String sea:str) {
                String sp = FcTool.getFc().indexFc(sea);
                String[] s2=sp.split(" ");
                for(int i=0;i<s2.length;i++){
                    if(i==0 && i==s2.length-1){
                        sbf.append(" (+"+s2[i]+") ");
                    }else if(i==0){
                        sbf.append(" (+"+s2[i]);
                    }
                    else if(i==s2.length-1){
                        sbf.append(" +"+s2[i]+") ");
                    }else{
                        sbf.append(" +"+s2[i]);
                    }
                }
            }
            sb.append("and match(t1.win_bid_company_fc) against('"
                    + sbf.toString() + "' in boolean mode) ");
        }
//        金额范围搜索
        if(itemAmount==1){
            sb.append(" and t1.win_bid_figure < 200000");
        }else if(itemAmount==2){
            sb.append(" and t1.win_bid_figure >=200000 and t1.win_bid_figure < 500000");
        }else if(itemAmount==3){
            sb.append(" and t1.win_bid_figure >=500000 and t1.win_bid_figure < 1000000");
        }else if(itemAmount==4){
            sb.append(" and t1.win_bid_figure >=1000000 and t1.win_bid_figure < 3000000");
        }else if(itemAmount==5){
            sb.append(" and t1.win_bid_figure >=3000000 and t1.win_bid_figure < 6000000");
        }else if(itemAmount==6){
            sb.append(" and t1.win_bid_figure >= 6000000 and t1.win_bid_figure <10000000");
        }else if(itemAmount==7){
            sb.append(" and t1.win_bid_figure >= 10000000");
        }



//        if (orderby==1){
//            sb.append(" order by t1.successful_time desc");
//        }
        if(title_key_order==1){
            sb.append(" order by t1.title desc ");
        }
        if(title_key_order==2){
            sb.append(" order by t1.title  ");
        }
        if(tenderId_order==1){
            sb.append(" order by t1.tender_id desc");
        }
        if(tenderId_order==2){
            sb.append(" order by t1.tender_id ");
        }
        if(time_order==1){
            sb.append(" order by t1.successful_time");
        }
        if(time_order==2){
            sb.append(" order by t1.successful_time desc ");
        }
        if(winBidCompany_order==1){
            sb.append(" order by t1.win_bid_company desc");
        }
        if(winBidCompany_order==2){
            sb.append(" order by t1.win_bid_company  ");
        }
        if(winBidFigure_order==1){
            sb.append(" order by t1.win_bid_figure desc");
        }
        if(winBidFigure_order==2){
            sb.append(" order by t1.win_bid_figure  ");
        }
        if(title_key_order==0 && tenderId_order==0&&time_order==0 && winBidCompany_order==0&&winBidFigure_order==0){
            sb.append(" order by t1.successful_time desc ");
        }


        // 判断是否为招标信息
//		sb.append("");
        //System.out.println(sb);
        Page<Record> newsPage = Db
                .paginate(
                        pags,
                        topNum,
                        select.toString(),
                        sb.toString(), new Object[]{startTime});
        return newsPage;
    }
    /**
     *
     * @param etime 截止日期
     * @param map 用户设置的条件
     * @return
     *
     * 通过用户设置条件查询招标信息
     */
    public List<Record> userDailyMail(int etime,Map<String, List<Record>> map){
        StringBuffer sb = new StringBuffer();      //t1.num              //t1.pid
        sb.append("select t1.id,t1.qualification,t1.tender_id as num,t1.tender_news_id as pid,t1.title,t1.url,t1.subscriber,t1.stime,t1.etime,t1.item_amount,FROM_UNIXTIME(t1.etime,'%Y-%m-%d') as endTime,FROM_UNIXTIME(t1.stime,'%Y-%m-%d') as startTime,FROM_UNIXTIME(t1.timelong,'%Y-%m-%d') as tlong,t1.timelong "
                + "from "+ Constants.TENDER_NEWS_INFO+" as t1  where ");
        //tendernews_info                            //gd
        String s = SqlUtil.getNewsWithUser(map, "t1.");
        // 添加用户设置条件
        if (!s.isEmpty() && !s.endsWith("and")) {
            sb.append(s + " and");
        } else {
            sb.append(s);
        }
        //t1.province
        sb.append("  etime>? and status=0   order by t1.timelong");
        sb.append(" desc");
        return Db.find(sb.toString(), etime);
    }

//    public List<Record> userNewsDailyMail(int etime,Map<String, List<Record>> map){
//        StringBuffer sb = new StringBuffer();          //t1.num     //t1.pid
//        sb.append("select t1.id,t1.qualification,t1.tender_id,t1.tender_news_id as pid,t1.title,t3.name as location,t1.url,t1.subscriber,t1.stime,t1.etime,t1.item_amount,FROM_UNIXTIME(t1.etime,'%Y-%m-%d') as endTime,FROM_UNIXTIME(t1.stime,'%Y-%m-%d') as startTime,t1.timelong "
//                + "from "+ Constants.TENDER_NEWS_INFO+" as t1 join "+ Constants.GD+" as t3 where ");
//        //tendernews_info                               //gd
//        String s = SqlUtil.getNewsWithUser(map, "t1.");
//        System.out.println(s);
//        // 添加用户设置条件
//        if (!s.isEmpty() && !s.endsWith("and")) {
//            sb.append(s + " and");
//        } else {
//            sb.append(s);
//        }
//        // t1.province
//        sb.append(" t1.province_id =  t3.id and etime>? and timelong > ? and status=0  order by t1.etime");
//        sb.append(" desc");
//        return Db.find(sb.toString(), new Object[]{etime, DateUtil.getNowToInt() - 60 * 60 * 24 });
//    }

    /**
     *
     * @param etime 截止日期
     * @param map 用户设置条件
     * @return
     * 通过用户设置的条件出现招标信息，信息为一天内出现的新信息
     */
    public List<Record> userNewsDailyMail(int etime,Map<String, List<Record>> map){
        StringBuffer sb = new StringBuffer();          //t1.num     //t1.pid
        sb.append("select t1.id,t1.qualification,t1.tender_id as num,t1.tender_news_id as pid,t1.title,t1.url,t1.subscriber,t1.stime,t1.etime,t1.item_amount,FROM_UNIXTIME(t1.etime,'%Y-%m-%d') as endTime,FROM_UNIXTIME(t1.stime,'%Y-%m-%d') as startTime,FROM_UNIXTIME(t1.timelong,'%Y-%m-%d') as tlong,t1.timelong "
                + "from "+ Constants.TENDER_NEWS_INFO+" as t1  where ");
        //tendernews_info                               //gd
        String s = SqlUtil.getNewsWithUser(map, "t1.");
        // 添加用户设置条件
        if (!s.isEmpty() && !s.endsWith("and")) {
            sb.append(s + " and");
        } else {
            sb.append(s);
        }
        // t1.province
        sb.append(" etime>? and timelong > ? and status=0  order by t1.timelong");
        sb.append(" desc");
        return Db.find(sb.toString(), new Object[]{etime, DateUtil.getNowToInt() - 60 * 60 * 24 });
    }


    /**
     *
     * @param userId
     * @param dateTime 截止日期
     * @param map 用户设置的条件
     * @return
     * 通过用户设置的条件查询出中标信息，且该中标信息必须有对应的招标信息，通过招标中标编号关联
     */
    @Override
    public List<TenderNews> findSuccessfulNewsForUser(int userId, int dateTime, Map<String, List<Record>> map) {
        List<TenderNews> tendernews=new ArrayList<TenderNews>();
//        Map<String, List<Record>> map= UserServices.getUserInfo(userId);
        StringBuffer sb=new StringBuffer();
        sb.append("select t1.id,t1.tender_id,t1.win_bid_company,t1.win_bid_figure from "+ Constants.SUCCESSFUL_TENDER_NEWS_INFO+" as t1 JOIN " +
                //num                                                           //tenderresultnews_info
                "(select tender_id as num from "+ Constants.TENDER_NEWS_INFO+" where ");
        //tendernews_info
        String s = SqlUtil.getNewsWithUser(map, Constants.TENDER_NEWS_INFO + ".");
        // 添加用户设置条件
        if (!s.isEmpty() && !s.endsWith("and")) {
            sb.append(s + " and");
        } else {
            sb.append(s);
        }                                   //num
        sb.append(" etime<?  and status=0 ) as t2 ON t2.num = t1.tender_id and t2.num!='' where t1.status = 0 and t1.win_bid_company!='' ");

        List<Record> recordlist= Db.find(sb.toString(), new Object[]{dateTime});
//        将recordlist遍历，以对象TenderNews形式返回
        for (Record record1 : recordlist) {
            TenderNews tenderNews = new TenderNews();
            tenderNews.setInfoId(record1.getInt("id"));
            tenderNews.setTenderId(record1.getStr("tender_id"));
            tenderNews.setWinBidCompany(record1.getStr("win_bid_company"));
            tenderNews.setWinBidFigure(record1.getDouble("win_bid_figure"));
            tendernews.add(tenderNews);
        }
        return tendernews;
    }

}
